const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 加载bodyParser模块
const bodyParser = require('body-parser');

// 加载MD5模块
const md5 = require('md5');

// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: 'root',        //数据库用户密码
  database: 'gather',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});

// 创建服务器对象
const app = express();

app.use(bodyParser.urlencoded({
  extended: false
}));


// 加载CORS模块
const cors = require('cors');

// 使用CORS中间件
app.use(cors({
  origin: ['http://localhost:8080', 'http://127.0.0.1:8080']
}));
// 小区名称接口
app.get('/plot', (req, res) => {
    let sql = 'SELECT pid,pname FROM plot ORDER BY pid';
    pool.query(sql, (error, results) => {
      if (error) throw error;
      res.send({ message: 'ok', code: 200, results: results });
    });
  });
// 小区学校名称接口
app.get('/city',(req,res)=>{

  let pid=req.query.pid;

  let sql='select sname,sid,pname from school,plot where pid=spid and pid=?';
  pool.query(sql,[pid],(error,results)=>{
    if (error) throw error;
    res.send({message:'ok', code: 200, results:results })
  })
});
// 学校介绍接口
app.get('/school',(req,res)=>{

  let sid=req.query.sid;

  let sql='select sname,sintro from school where sid=?';
  pool.query(sql,[sid],(error,results)=>{
    if (error) throw error;
    res.send({message:'ok', code: 200, results:results })
  })
});

app.get('/schoolmore', (req, res) => {
  // 获取客户端传递的pid参数
  let pid = req.query.pid;
  // 获取客户端传递的page参数
  let page = req.query.page? req.query.page : 1;
  // 存储每页显示的记录数
  let pagesize = 10;
  // 通过公式来计算从第几条记录开始返回
  let offset = (page - 1) * pagesize;
  // 用于存储获取到的总记录数
  let rowcount;
  // 获取指定分类下的文章总数
  let sql = 'SELECT COUNT(sid) AS count FROM school WHERE spid=?';
  pool.query(sql, [pid], (error, results) => {
    if (error) throw error;
    // 将获取到总记录数赋给rowcount变量
    rowcount = results[0].count;
    /**************************************************/
    // 根据总记录数和每页显示的记录数来计算总页数
    let pagecount = Math.ceil(rowcount / pagesize);
    // 查询SQL语句
    sql = '	select sname,sid,pname from school,plot where pid=spid and pid=? limit ?,?';
    // 执行SQL
    pool.query(sql, [pid, offset, pagesize], (error, results) => {
      if (error) throw error;
      res.send({ message: 'ok', code: 200, results: results, pagecount: pagecount });
    });
  });
});

// 注册接口
app.get('/userreg',(req,res)=>{

  let uname=req.query.uname;
  let ucid=(req.query.ucid).toLocaleLowerCase();
  let upwd=req.query.upwd;
  let uaqm=req.query.uaqm;
  let usex=req.query.usex;
  let ubor=req.query.ubor;
  var pdzc=true;
  let sql_out='select ucid from user_message';
  pool.query(sql_out,(error,results)=>{
    if (error) throw error;
    for(var i=0;i<results.length;i++){
      if(results[i].ucid==ucid){
        pdzc=false;
        break;
      }
    }
    if(pdzc==true){
      let sql='insert into user_message(uid,uname,ucid,upwd,uaqm,usex,ubor) value(null,?,?,?,?,?,?)';
      pool.query(sql,[uname,ucid,upwd,uaqm,usex,ubor],(error,results)=>{
        if (error) throw error;
        res.send({message:'ok', code: 200, results:results })
      })
    }else if(pdzc==false){
      res.send({message:'no',code:400})
    }
  })
  
});

// 修改密码接口
app.get('/userforget',(req,res)=>{

  let ucid=(req.query.ucid).toLocaleLowerCase();
  let upwd=req.query.upwd;
  let uaqm=req.query.uaqm;
  var pdxg=false;
  let sql_out='select ucid from user_message';
  pool.query(sql_out,(error,results)=>{
    if (error) throw error;
    for(var i=0;i<results.length;i++){
      if(results[i].ucid == ucid){
        pdxg=true;
        break;
      }
    }
    if(pdxg==true){
      let sql_out_out='select uaqm from user_message where ucid=?'
      pool.query(sql_out_out,[ucid],(error,results)=>{
        if (error) throw error;
        if(results[0].uaqm==uaqm){
          let sql='update user_message set upwd = ? where ucid = ?';
          pool.query(sql,[upwd,ucid],(error,results)=>{
            if (error) throw error;
            res.send({message:'ok', code: 200, results:results })
          })
        }else{
          res.send({message:'none',code:401});
        }
      })
    }else if(pdzc==false){
      res.send({message:'no',code:400})
    }
  })
  
});

// 登录接口
app.get('/userlogin',(req,res)=>{

  let ucid=req.query.ucid;
  let upwd=req.query.upwd;

  let sql='select * from user_message where ucid=? and upwd=?';
  pool.query(sql,[ucid,upwd],(error,results)=>{
    if (error) throw error;
    if(results.length != 0){
      res.send({message:'ok', code: 200, results:results })
    }else{
      res.send({message:'no',code:400})
    }
  })
});

app.get('/selectplot',(req,res)=>{
  let sql=`select * from plot order by pid`;
  pool.query(sql,(error,results)=>{
    if (error) throw error;
    res.send({message:'ok',code:200,results:results})
  })
})

app.get('/selectschool',(req,res)=>{
  let spid=req.query.spid;
  let sql=`select sid,sname from school where spid=? order by sid`;
  pool.query(sql,[spid],(error,results)=>{
    if (error) throw error;
    res.send({message:'ok',code:200,results:results})
  })
})

app.get('/messageset',(req,res)=>{
  let gname=req.query.gname;
  let gphon=req.query.gphon;
  let gcid=req.query.gcid;
  let uaddress=req.query.uaddress;
  let usid=req.query.usid;
  let ucid=req.query.ucid;

  let sql=`update user_message set gname=?,gphon=?,gcid=?,uaddress=?,usid=? where ucid = ?`
  pool.query(sql,[gname,gphon,gcid,uaddress,usid,ucid],(error,results)=>{
    if (error) throw error;
    res.send({message:'ok',code:200,results:results})
  })
});

app.get('/selectmessage',(req,res)=>{

  let uid=req.query.uid;

  let sql=`select * from user_message where uid=?`
  pool.query(sql,[uid],(error,results)=>{
    if (error) throw error;
    res.send({message:'ok',code:200,results:results})
  })
});

app.get('/dnschool',(req,res)=>{

  let sid=req.query.usid;

  let sql=`select sname from school where sid=?`
  pool.query(sql,[sid],(error,results)=>{
    if (error) throw error;
    res.send({message:'ok',code:200,results:results})
  })
});

  app.listen(3000, () => {
    console.log('server is running...');
  });